﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Xml;

namespace DAL.App_Code
{
    public class DBBase
    { 
        public DBBase()
        {
        }
        ~DBBase()     // 析构函数关闭连接
        {
            if (Connection.State == ConnectionState.Open)
            {
                Connection.Close();
            }
        }
        #region 参数和构造函数
        private static String connString;        // 连接字符串
        private static SqlConnection con;        // 数据库连接
        protected static SqlConnection Connection  // 获得连接
        {
            get
            {
                connString = ConfigurationManager.AppSettings["connString"];
                if (con == null)
                {
                    con = new SqlConnection(connString);
                    con.Open();
                }
                else if (con.State == ConnectionState.Closed)
                {
                    con.Open();
                }
                else if (con.State == ConnectionState.Broken)
                {
                    con.Close();
                    con.Open();
                }
                return con;
            }
        }
        #endregion

        #region 初始化查询命令
        /// <summary>
        /// 根据SQL语句和参数实例化SQlcommand
        /// </summary>
        /// <param name="sqlString"></param>
        /// <param name="parameter"></param>
        /// <param name="cmdType"></param>
        /// <returns></returns>
        private SqlCommand BuildCommand(String sqlString, IDataParameter[] parameter, CommandType cmdType)
        {
            SqlCommand cmd = new SqlCommand(sqlString, Connection);
            cmd.CommandType = cmdType;
            if (parameter != null)
            {
                cmd.Parameters.AddRange(parameter);  // 直接将数组复制
            }
            return cmd;
        }
        /// <summary>
        /// 根据SQL语句和参数实例化SQlcommand并且带 返回值
        /// </summary>
        /// <param name="sqlString"></param>
        /// <param name="parameter"></param>
        /// <param name="cmdType"></param>
        /// <returns></returns>
        protected SqlCommand BuildRetCommand(String sqlString, IDataParameter[] parameter, CommandType cmdType)
        {
            SqlCommand cmd = BuildCommand(sqlString, parameter, cmdType);
            cmd.Parameters.Add(new SqlParameter("ReturnValue", /* 返回值参数名称 */
                    SqlDbType.Int,                  /* 参数类型为整型 */
                    4,	                            /* 大小 */
                    ParameterDirection.ReturnValue, /* 表示为返回值 */
                    false,                          /* 不能为空 */
                    0,                              /* 字节精度 */
                    0,                              /* 小数位数 */
                    string.Empty,                   /* 源列名称 */
                    DataRowVersion.Default,         /* 版本     */
                    null));

            return cmd;
        }
        /// <summary>
        /// 根据SQL语句和参数实例化SQlcommand并且带 输出参数
        /// </summary>
        /// <param name="sqlString"></param>
        /// <param name="parameter"></param>
        /// <param name="cmdType"></param>
        /// <returns></returns>
        protected SqlCommand BuildOutCommand(String sqlString, IDataParameter[] parameter, CommandType cmdType)
        {
            SqlCommand cmd = BuildCommand(sqlString, parameter, cmdType);
            cmd.Parameters.Add(new SqlParameter("@output", /* 输出值参数名称 */
                    SqlDbType.Int,                  /* 参数类型为整型 */
                    4,	                            /* 大小 */
                    ParameterDirection.Output,      /* 表示为返回值 */
                    false,                          /* 不能为空 */
                    0,                              /* 字节精度 */
                    0,                              /* 小数位数 */
                    string.Empty,                   /* 源列名称 */
                    DataRowVersion .Default,        /* 版本     */
                    null));
            return cmd;
        }
        #endregion

        #region 基本查询支持存储过程和一般SQL语句
        /// <summary>
        /// 执行SQL语句，支持存储过程和一般语句
        /// 从affectedRow获得影响的行数
        /// 也可获得语句中的      返回值
        /// </summary>
        /// <param name="sqlString"></param>
        /// <param name="parameter"></param>
        /// <param name="cmdType"></param>
        /// <param name="affectedRow"></param>
        /// <returns></returns>
        private int ExecuteRetQuery(String sqlString, IDataParameter[] parameter, CommandType cmdType, out int affectedRow)
        {
            int RetValue = 0;  // 返回值
            SqlCommand cmd = BuildRetCommand(sqlString, parameter, cmdType);
            if (Connection.State != ConnectionState.Open)
            {
                Connection.Open();
            }
            try
            {
                affectedRow = cmd.ExecuteNonQuery(); // 影响的行数
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                Connection.Close();
            }

            RetValue = (int)cmd.Parameters["ReturnValue"].Value;
            return RetValue;
        }
        /// <summary>
        /// 执行SQL语句，支持存储过程和一般语句
        /// 从affectedRow获得影响的行数
        /// 也可获得语句中的       输出值
        /// </summary>
        /// <param name="sqlString"></param>
        /// <param name="parameter"></param>
        /// <param name="cmdType"></param>
        /// <param name="affectedRow"></param>
        /// <returns></returns>
        private int ExecuteOutQuery(String sqlString, IDataParameter[] parameter, CommandType cmdType, out int affectedRow)
        {
            int OutValue = 0;  // 输出值
            SqlCommand cmd = BuildOutCommand(sqlString, parameter, cmdType);
            if (Connection.State != ConnectionState.Open)
            {
                Connection.Open();
            }
            try
            {
                affectedRow = cmd.ExecuteNonQuery(); // 影响的行数
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                Connection.Close();
            }

            OutValue = (int)cmd.Parameters["@output"].Value;
            return OutValue;
        }
        /// <summary>
        /// 执行SQL语句，支持存储过程和一般语句
        /// 返回DataReader形式的结果
        /// </summary>
        /// <param name="sqlString"></param>
        /// <param name="parameter"></param>
        /// <param name="cmdType"></param>
        /// <returns></returns>
        private SqlDataReader ExecuteQuery(String sqlString, IDataParameter[] parameter, CommandType cmdType)
        {
            SqlDataReader dr;
            SqlCommand cmd = BuildCommand(sqlString, parameter, cmdType);
            if (Connection.State != ConnectionState.Open)
            {
                Connection.Open();
            }
            try
            {
                dr = cmd.ExecuteReader();
            }
            catch (Exception e)
            {
                throw e;
            }
            return dr;
        }
        /// <summary>
        /// 执行SQL语句
        /// 返回DataSet结果集
        /// </summary>
        /// <param name="sqlString"></param>
        /// <param name="parameter"></param>
        /// <param name="cmdType"></param>
        /// <param name="table"></param>
        /// <returns></returns>
        private DataSet ExecuteQuery(String sqlString, IDataParameter[] parameter, CommandType cmdType, String table)
        {
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter();
            ds.Dispose();         // 清空结果集
            da.Dispose();         // 清空数据集
            da.SelectCommand = BuildCommand(sqlString, parameter, cmdType);
            if (Connection.State != ConnectionState.Open)
            {
                Connection.Open();
            }
            try
            {
                da.Fill(ds, table);
                return ds;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                Connection.Close();
            }
        }
        ///// <summary>
        ///// 返回List 
        ///// </summary>
        ///// <param name="sqlString"></param>
        ///// <param name="parameter"></param>
        ///// <param name="cmdType"></param>
        ///// <returns></returns>
        //private List<Object> ExecuteQuery(String sqlString, IDataParameter[] parameter, CommandType cmdType)
        //{
        //    List<Object> list = new List<object>();
        //    DataSet ds = new DataSet();
        //    DataTable table = new DataTable();
        //    SqlDataAdapter da = new SqlDataAdapter();
        //    da.SelectCommand = BuildCommand(sqlString, parameter, cmdType);
        //    if (Connection.State != ConnectionState.Open)
        //        Connection.Open();
        //    try
        //    {
        //        da.Fill(ds, table);
        //        table = ds.Tables[0];
     
        //        foreach (DataRow row in table.Rows)
        //        {
        //            List<Object> rowlist = new List<object>();
        //            foreach (DataColumn column in table.Columns)
        //            {
        //                rowlist.Add(row[column]);
        //            }
        //            list.Add(rowlist);
        //        }
        //        return list;
        //    }
        //    catch (Exception e)
        //    {
        //        throw e;
        //    }
        //    finally
        //    {
        //        Connection.Close();
        //    }
        //}
        /// <summary>
        /// 将结果放到指定数据集的表中
        /// </summary>
        /// <param name="sqlString"></param>
        /// <param name="parameter"></param>
        /// <param name="cmdType"></param>
        /// <param name="ds"></param>
        /// <param name="table"></param>
        private void ExecuteQuery(String sqlString, IDataParameter[] parameter, CommandType cmdType, DataSet ds, String table)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            ds.Dispose();
            da.SelectCommand = BuildCommand(sqlString, parameter, cmdType);
            if (Connection.State != ConnectionState.Open)
            {
                Connection.Open();
            }
            try
            {
                da.Fill(ds, table);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                Connection.Close();
            }
        }
        /// <summary>
        /// 无返回值  可获得影响行数
        /// </summary>
        /// <param name="sqlString"></param>
        /// <param name="parameter"></param>
        /// <param name="cmdType"></param>
        /// <param name="affectedRow"></param>
        private void ExecuteQuery(String sqlString, IDataParameter[] parameter, CommandType cmdType, out int affectedRow)
        {
            SqlCommand cmd = BuildCommand(sqlString, parameter, cmdType);
            if (Connection.State != ConnectionState.Open)
            {
                Connection.Open();
            }
            try
            {
                affectedRow = cmd.ExecuteNonQuery(); // 影响的行数
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                Connection.Close();
            }
        }
        /// <summary>
        /// 执行返回XML的查询
        /// </summary>
        /// <param name="sqlString"></param>
        /// <param name="parameter"></param>
        /// <param name="cmdType"></param>
        /// <returns></returns>
        protected XmlDocument ExecuteQuery_XML(String sqlString, IDataParameter[] parameter, CommandType cmdType)
        {
            XmlDocument xml = new XmlDocument();
            SqlCommand cmd = BuildCommand(sqlString, parameter, cmdType);
            if (Connection.State != ConnectionState.Open)
                Connection.Open();
            try
            {
                xml.Load(cmd.ExecuteXmlReader());
                return xml;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                Connection.Close();
            }
        }
        #endregion

        #region 执行存储过程接口
        /// <summary>
        /// 执行存储过程 返回值
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="parameter"></param>
        /// <param name="affectedRow"></param>
        /// <returns></returns>
        protected int ExecuteRetProcedure(String procName, IDataParameter[] parameter, out int affectedRow)
        {
            return ExecuteRetQuery(procName, parameter, CommandType.StoredProcedure, out affectedRow);
        }
        /// <summary>
        /// 执行存储过程 输出值
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="parameter"></param>
        /// <param name="affectedRow"></param>
        /// <returns></returns>
        protected int ExecuteOutProcedure(String procName, IDataParameter[] parameter, out int affectedRow)
        {
            return ExecuteOutQuery(procName, parameter, CommandType.StoredProcedure, out affectedRow);
        }
        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="parameter"></param>
        /// <returns></returns>
        protected SqlDataReader ExecuteProcedure(String procName, IDataParameter[] parameter)
        {
            return ExecuteQuery(procName, parameter, CommandType.StoredProcedure);
        }
        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="parameter"></param>
        /// <param name="table"></param>
        /// <returns></returns>
        protected DataSet ExecuteProcedure(String procName, IDataParameter[] parameter, String table)
        {
            return ExecuteQuery(procName, parameter, CommandType.StoredProcedure, table);
        }
        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="parameter"></param>
        /// <param name="ds"></param>
        /// <param name="table"></param>
        protected void ExecuteProcedure(String procName, IDataParameter[] parameter, DataSet ds, String table)
        {
            ExecuteQuery(procName, parameter, CommandType.StoredProcedure, ds, table);
        }
        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="parameter"></param>
        /// <param name="affectedRow"></param>
        protected void ExecuteProcedure(String procName, IDataParameter[] parameter, out int affectedRow)
        {
            ExecuteQuery(procName, parameter, CommandType.StoredProcedure, out affectedRow);
        }
        #endregion

        #region 执行连接字符串接口
        /// <summary>
        /// 执行连接字符串 返回值
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="parameter"></param>
        /// <param name="affectedRow"></param>
        /// <returns></returns>
        protected int ExecuteRetString(String sqlString, IDataParameter[] parameter, out int affectedRow)
        {
            return ExecuteRetQuery(sqlString, parameter, CommandType.Text, out affectedRow);
        }
        /// <summary>
        /// 执行连接字符串 输出值
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="parameter"></param>
        /// <param name="affectedRow"></param>
        /// <returns></returns>
        protected int ExecuteOutString(String sqlString, IDataParameter[] parameter, out int affectedRow)
        {
            return ExecuteOutQuery(sqlString, parameter, CommandType.Text, out affectedRow);
        }
        /// <summary>
        /// 执行连接字符串
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="parameter"></param>
        /// <returns></returns>
        protected SqlDataReader ExecuteString(String sqlString, IDataParameter[] parameter)
        {
            return ExecuteQuery(sqlString, parameter, CommandType.Text);
        }
        /// <summary>
        /// 执行连接字符串
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="parameter"></param>
        /// <param name="table"></param>
        /// <returns></returns>
        protected DataSet ExecuteString(String sqlString, IDataParameter[] parameter, String table)
        {
            return ExecuteQuery(sqlString, parameter, CommandType.Text, table);
        }
        /// <summary>
        /// 执行连接字符串 填充dataset
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="parameter"></param>
        /// <param name="ds"></param>
        /// <param name="table"></param>
        protected void ExecuteString(String sqlString, IDataParameter[] parameter, DataSet ds, String table)
        {
            ExecuteQuery(sqlString, parameter, CommandType.Text, ds, table);
        }
        /// <summary>
        /// 执行连接字符，影响行数
        /// </summary>
        /// <param name="sqlString"></param>
        /// <param name="parameter"></param>
        /// <param name="affectedRow"></param>
        protected void ExecuteString(String sqlString, IDataParameter[] parameter, out int affectedRow)
        {
            ExecuteQuery(sqlString, parameter, CommandType.Text, out affectedRow);
        }
        /// <summary>
        /// 返回查询的XML文档
        /// </summary>
        /// <param name="sqlString"></param>
        /// <param name="parameter"></param>
        /// <returns></returns>
        protected XmlDocument ExecuteString_XML(String sqlString, IDataParameter[] parameter)
        {
            return ExecuteQuery_XML(sqlString, parameter, CommandType.Text);
        }
        #endregion    
    }

}
